﻿using System;
using System.Globalization;
using Finisar.SQLite;
using System.IO;

namespace MyMovies
{
    internal class TableMovies
    {
        private static string create = @"CREATE TABLE [Movies] (
                                    [ID] INTEGER  NOT NULL PRIMARY KEY,
                                    [Title] VARCHAR(256)  NULL,
                                    [Year] INTEGER  NULL,
                                    [Genre] INTEGER  NULL,
                                    [Path] VARCHAR(256)  NULL,
                                    [Added] DATE  NULL,
                                    [Size] NUMERIC NULL,
                                    [TimeStamp] DATE NULL
                                    );
                                CREATE INDEX [Movies_IX1] ON [Movies](
                                    [Title]  ASC
                                    );";

        public static bool Create()
        {
            return SqlLiteManagement.ExecuteNonQuery(create) >= 0;
        }

        public static string SearchMovie(string title, DateTime scanDate)
        {
            int ID = -1;
            string path = string.Empty;
            DateTime date = DateTime.Today;

            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.String);
            p1.Value = title;

            string query = "SELECT [ID], [Path], [TIMESTAMP] FROM [Movies] WHERE [Title] = @p1";

            if (SqlLiteManagement.ExecuteReader(query, new SQLiteParameter[] { p1 }) != null)
            {
                while (SqlLiteManagement.Read())
                {
                    ID = SqlLiteManagement.GetValue<int>("[ID]");
                    path = SqlLiteManagement.GetValue<string>("[Path]");
                    date = SqlLiteManagement.GetValue<DateTime>("[TIMESTAMP]");
                }
            }
            SqlLiteManagement.ReaderClose();

            if (date.ToString() != scanDate.ToString() && !string.IsNullOrEmpty(path))
                UpdateTimeStamp(ID, scanDate);

            return path;
        }

        public static string FindTitleByID(int id)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;

            string query = "SELECT [Description] FROM [Movies] WHERE [ID] = @p1";

            object result = SqlLiteManagement.ExecuteScalar(query, new SQLiteParameter[] { p1 });

            return result == null
                ? string.Empty
                : (string)Convert.ChangeType(result, typeof(string));
        }

        public static string FindPathByID(int id)
        {
            string filePath = string.Empty;

            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;

            string query = "SELECT [Path], [Title] FROM [Movies] WHERE [ID] = @p1";

            if (SqlLiteManagement.ExecuteReader(query, new SQLiteParameter[] { p1 }) != null)
            {
                if(SqlLiteManagement.Read())
                {
                    string title = SqlLiteManagement.GetValue<string>("[Title]");
                    string path = SqlLiteManagement.GetValue<string>("[Path]");
                    filePath = Path.Combine(path, title);
                }

            }
            SqlLiteManagement.ReaderClose();

            return filePath;
        }

        public static int FindIDByFilenamePath(string title, string path)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.String);
            p1.Value = path;
            SQLiteParameter p2 = new SQLiteParameter("@p2", System.Data.DbType.String);
            p2.Value = title;

            string query = "SELECT [ID] FROM [Movies] WHERE [Path] = @p1 AND [Title] = @p2";

            object result = SqlLiteManagement.ExecuteScalar(query, new SQLiteParameter[] { p1, p2 });

            return result == null
                ? -1
                : (int)Convert.ChangeType(result, typeof(int));
        }

        public static string SearchInsert(string title, int genre, string path, DateTime scandate)
        {
            string mypath = SearchMovie(title, scandate);
            if (!string.IsNullOrEmpty(mypath))
                return mypath;

            string filename = System.IO.Path.Combine(path, title);
            System.IO.FileInfo fInfo = new System.IO.FileInfo(filename);

            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.String);
            p1.Value = title;
            SQLiteParameter p2 = new SQLiteParameter("@p2", System.Data.DbType.String);
            path = path.Remove(0, path.IndexOf("Video") + 6);
            p2.Value = path;
            SQLiteParameter p3 = new SQLiteParameter("@p3", System.Data.DbType.DateTime);
            p3.Value = scandate;

            string query = string.Format("INSERT INTO [Movies] ([Title],[Genre], [Path],[Added], [Size], [TIMESTAMP]) VALUES ( @p1, '{0}', @p2, '{1}', {2}, @p3)", genre, fInfo.CreationTime.ToString("yyyyMMdd"), fInfo.Length);
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1, p2, p3 });

            if (result > 0)
            {
                int id = FindIDByFilenamePath(title, path);
                MediaInfoParser.Parse(id, filename);
            }

            return SearchMovie(title, scandate);
        }

        public static void ChangeCreationDate(int ID, DateTime now)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = ID;
            string query = string.Format("UPDATE [Movies] SET [Added] = '{0}' WHERE [ID] = @p1", now.ToString("yyyyMMdd"));
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }

        public static void Fill(System.Windows.Forms.ListView.ListViewItemCollection items, System.Windows.Forms.ListViewGroupCollection groups)
        {
            NumberFormatInfo nfi = new CultureInfo(CultureInfo.CurrentCulture.Name, false).NumberFormat;
            nfi.NumberDecimalDigits = 0;

            items.Clear();

            string query = "SELECT [ID],[Title],[Genre],[Path],[Added],[Size] FROM [Movies] ORDER BY [Title]";

            if (SqlLiteManagement.ExecuteReader(query) != null)
            {
                while (SqlLiteManagement.Read())
                {
                    string title = SqlLiteManagement.GetValue<string>("[Title]");
                    string genre = TableGenre.FindGenreByID(SqlLiteManagement.GetValue<int>("[Genre]"));
                    long size = SqlLiteManagement.GetValue<long>("[Size]") / 1000;
                    DateTime added = SqlLiteManagement.GetValue<DateTime>("[Added]");
                    System.Windows.Forms.ListViewItem lvi = new System.Windows.Forms.ListViewItem(title);
                    lvi.SubItems.Add(genre).Name = "colGenre";
                    lvi.SubItems.Add(added.ToString("MMMM yy")).Name = "colDownload";
                    lvi.SubItems["colDownload"].Tag = added.ToString("yyyyMM");
                    lvi.SubItems.Add(string.Concat(size.ToString("N", nfi), " KB")).Name = "colSize";
                    lvi.SubItems["colSize"].Tag = size;
                    lvi.Tag = SqlLiteManagement.GetValue<int>("[ID]");
                    lvi.Group = groups[genre];
                    items.Add(lvi);
                }
            }
            SqlLiteManagement.ReaderClose();
        }

        public static void RemoveALL()
        {
            string query = "DELETE FROM [Movies]";
            SqlLiteManagement.ExecuteNonQuery(query);
        }

        public static void RemoveOld(DateTime scanDate)
        {
            string query = "DELETE FROM [Movies] WHERE [TIMESTAMP] < @p1";
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.DateTime);
            p1.Value = scanDate;

            SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }

        public static void RemoveByID(int id)
        {
            string query = "DELETE FROM [Movies] WHERE [ID] = @p1";
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;
            SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }

        public static void UpdateTimeStamp(int ID, DateTime scanDate)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.DateTime);
            p1.Value = scanDate;

            string query = string.Format("UPDATE [Movies] SET [TIMESTAMP] = @p1 WHERE [ID] = '{0}'", ID.ToString());
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }
    }
}